﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace produccion.clases
{
    public class CLSMAEMAT:ObservableObject
    {

        MDOgeneral mod = new MDOgeneral();
   
        #region propiedades
        string codmat_mat, nommat_mat, codgrp_mat, codsub_mat, uniusu_mat, unicom_mat, estado_mat, posbod_mat;

        public string CODMAT_MAT
        {
            get { return codmat_mat; }
            set { codmat_mat = value; RaisePropertyChangedEvent("CODMAT_MAT"); }
        }
        public string NOMMAT_MAT
        {
            get { return nommat_mat; }
            set 
            {
                if (nommat_mat != value) { CAMBIO = true; }
                nommat_mat = value;
                RaisePropertyChangedEvent("NOMMAT_MAT");
            }
        }
        public string CODGRP_MAT
        {
            get { return codgrp_mat; }
            set 
            {
                if (codgrp_mat != value) { CAMBIO = true; }
                codgrp_mat = value;
                RaisePropertyChangedEvent("CODGRP_MAT");
            }
        }
        public string CODSUB_MAT
        {
            get { return codsub_mat; }
            set 
            {
                if (codsub_mat != value) { CAMBIO = true; }
                codsub_mat=value;
                RaisePropertyChangedEvent("CODSUB_MAT");
            }
        }
        public string UNIUSU_MAT
        {
            get { return uniusu_mat; }
            set 
            {
                if (uniusu_mat != value) { CAMBIO = true; }
                uniusu_mat = value;
                RaisePropertyChangedEvent("UNIUSU_MAT");
            }
        }
        public string UNICOM_MAT
        {
            get { return unicom_mat; }
            set 
            {
                if (unicom_mat != value) { CAMBIO = true; }
                unicom_mat = value;
                RaisePropertyChangedEvent("UNICOM_MAT");
            }
        }
        public string ESTADO_MAT
        {
            get { return estado_mat; }
            set 
            {
                if (estado_mat != value) { CAMBIO = true; }
                estado_mat = value;
                RaisePropertyChangedEvent("ESTADO_MAT");
            }
        }
        public string POSBOD_MAT
        {
            get { return posbod_mat; }
            set 
            {
                if (posbod_mat != value) { CAMBIO = true; }
                posbod_mat = value;
                RaisePropertyChangedEvent("POSBOD_MAT");
            }
        }
        private Boolean _EXISTE = false;
        public Boolean EXISTE
        {
            get { return _EXISTE; }
            set { _EXISTE = value; RaisePropertyChangedEvent("EXISTE"); }
        }
        private Boolean _CAMBIO = false;
        public Boolean CAMBIO
        {
            get { return _CAMBIO; }
            set { _CAMBIO = value; RaisePropertyChangedEvent("CAMBIO"); }
        }

        #endregion
       
        #region metodos

        public List<CLSMAEMAT> LisMateriales() 
        {
            try 
            {
                string query = "select codmat_mat,nommat_mat from maemat order by codmat_mat";
                DataTable val = mod.consulta(query);
                List<CLSMAEMAT> lisMat = new List<CLSMAEMAT>();
                foreach (DataRow r in val.Rows) 
                {
                    lisMat.Add(new CLSMAEMAT { 
                    CODMAT_MAT=r["CODMAT_MAT"].ToString().Trim(),
                    NOMMAT_MAT=r["NOMMAT_MAT"].ToString().Trim()
                    });
        
                }
                return lisMat;
            }
            catch (Exception ex) { throw ex; }
        }


        public int eliminar() 
        {
            int i = 0;
            try 
            {
                if (!exiMov())
                {
                    MDOgeneral.transaccion = MDOgeneral.conexion.BeginTransaction();
                    new CLSPRVMAT { CODMAT_PXM = CODMAT_MAT }.eliProMat();
                    new CLSMAEMTP { CODMAT_MTP = CODMAT_MAT }.eliMatMat();

                    string query = "delete from MAEMAT where CODMAT_MAT='" + CODMAT_MAT + "'";
                    i = mod.comando(query);
                    MDOgeneral.transaccion.Commit();

                    return i;
                }
                return 0;
            } 
            catch(Exception ex){ MDOgeneral.transaccion.Rollback(); throw(ex);  }
        }

        public void LimpiarValores()
        {
            CODMAT_MAT = "";
            NOMMAT_MAT = "";
            CODGRP_MAT = "";
            CODSUB_MAT = "";
            UNICOM_MAT = "";
            UNIUSU_MAT = "";
            ESTADO_MAT = "";
            POSBOD_MAT = "";
        }
        
        public Boolean ExisteMateria()
        {
            try
            {
                string query = "select codmat_mat from maemat where codmat_mat='" + CODMAT_MAT + "'";
                if (mod.consulta(query).Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Consultar()
        {
            try
            {
                string query = "select codmat_mat as Codigo, nommat_mat as Nombre, unicom_mat, uniusu_mat from maemat where codmat_mat='" + CODMAT_MAT + "' order by codmat_mat ";
                DataTable dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    this.NOMMAT_MAT = dt.Rows[0][1].ToString();
                    this.UNICOM_MAT = dt.Rows[0][2].ToString();
                    this.UNIUSU_MAT = dt.Rows[0][3].ToString();

                    this.EXISTE = true;
                    this.CAMBIO = false;
                }
                else
                {
                    this.NOMMAT_MAT = "";
                    this.EXISTE = false;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Buscar()
        {
            try
            {
                string query = "select CODMAT_MAT as Codigo, NOMMAT_MAT as Nombre from MAEMAT where ";
                if (NOMMAT_MAT != "")
                {
                    query = query + "UPPER(NOMMAT_MAT) like '%" + NOMMAT_MAT.ToUpper() + "%' or UPPER(CODMAT_MAT) like '%" + NOMMAT_MAT.ToUpper() + "%' and ";
                }
                query = query.Remove(query.Length - 4).Trim() + " Order by codmat_mat asc";

                return mod.consulta(query);
            }
            catch (Exception e) { throw e; }
        }
        
        public DataTable BuscarMatp()
        {
            try
            {
                string query = " select CODMAT_MAT||CODCOL_MTP as Codigo,NOMMAT_MAT as Nombre from maemat,maemtp where CODMAT_MAT=CODMAT_MTP and  UPPER(NOMMAT_MAT) like '%" + NOMMAT_MAT.ToUpper() + "%' order by codmat_mat ";
                return mod.consulta(query);
            }
            catch (Exception e) { throw e; }
        }

        public void ConsMat() 
        {
            try 
            {
                string query = "select CODMAT_MAT,NOMMAT_MAT,CODGRP_MAT,CODSUB_MAT,UNIUSU_MAT,UNICOM_MAT,ESTADO_MAT,POSBOD_MAT from MAEMAT where CODMAT_MAT='" + CODMAT_MAT+"' order by codmat_mat ";
                DataTable val = mod.consulta(query);

                if (val.Rows.Count > 0)
                {
                    CODMAT_MAT = val.Rows[0]["CODMAT_MAT"].ToString();
                    NOMMAT_MAT = val.Rows[0]["NOMMAT_MAT"].ToString();
                    CODGRP_MAT = val.Rows[0]["CODGRP_MAT"].ToString();
                    CODSUB_MAT = val.Rows[0]["CODSUB_MAT"].ToString();
                    UNIUSU_MAT = val.Rows[0]["UNIUSU_MAT"].ToString();
                    UNICOM_MAT = val.Rows[0]["UNICOM_MAT"].ToString();
                    ESTADO_MAT = val.Rows[0]["ESTADO_MAT"].ToString();
                    POSBOD_MAT = val.Rows[0]["POSBOD_MAT"].ToString();
                    EXISTE = true;
                    CAMBIO = false;
                }
                else 
                {
                    EXISTE = false;
                    CAMBIO = false;
                }

            }
            catch (Exception ex) { throw (ex); }
        }

        public int actualizar() 
        {
            try 
            {
                string query = "update MAEMAT set NOMMAT_MAT='"+NOMMAT_MAT+"',CODGRP_MAT='"+CODGRP_MAT+"',CODSUB_MAT='"+CODSUB_MAT+"',UNIUSU_MAT='"+UNIUSU_MAT+"',UNICOM_MAT='"+UNICOM_MAT+"',ESTADO_MAT='"+ESTADO_MAT+"',POSBOD_MAT='"+POSBOD_MAT+"' where CODMAT_MAT='"+CODMAT_MAT+"'";
                return mod.comando(query);
            }
            catch(Exception ex){ throw(ex);}
        }

        public int insertar()
        {
            try
            {
                string query = "insert into MAEMAT (CODMAT_MAT,NOMMAT_MAT,CODGRP_MAT,CODSUB_MAT,UNIUSU_MAT,UNICOM_MAT,ESTADO_MAT,POSBOD_MAT) values ('"+CODMAT_MAT+"','"+NOMMAT_MAT+"','"+CODGRP_MAT+"','"+CODSUB_MAT+"','"+UNIUSU_MAT+"','"+UNICOM_MAT+"','"+ESTADO_MAT+"','"+POSBOD_MAT+"')";
                return mod.comando(query);
            }
            catch (Exception ex) { throw (ex); }
        }

        public Boolean guardarMaterial(CLSMAEMAT material,List<CLSPRVMAT> LisProveedor,List<CLSMAEMTP> LisMateriap)
        {
            String StrCodigoMaterial = "";//FJRF - Variable que contendra el codigo del material

            Boolean sw = true;
            try 
            {
                StrCodigoMaterial = material.CODMAT_MAT.ToString();//FJRF - Asignamos el codigo del material

                MDOgeneral.transaccion = MDOgeneral.conexion.BeginTransaction();
                new CLSMAEMTP().guardarMat(LisMateriap);
                new CLSPRVMAT().guardarPrv(StrCodigoMaterial, LisProveedor);
                
                if (material.EXISTE)
                {
                    if (material.actualizar() > 0)
                    {
                        material.CAMBIO = false;
                    }
                    else
                    {
                        sw = false;
                    }
                }
                else
                {
                    if (material.insertar() > 0)
                    {
                        material.CAMBIO = false;
                        material.EXISTE = true;
                    }
                    else
                    {
                        sw = false;
                    }
                }
                MDOgeneral.transaccion.Commit();               
            }
            catch (Exception ex) {  MDOgeneral.transaccion.Rollback();   throw (ex); }

            return sw;
        }

        public string CrearMaterial()
        {
            try 
            {
                MDOgeneral.spComando.CommandText = "SP_CREMAT";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;

                MDOgeneral.spComando.Parameters.Clear();
                //Input
                MDOgeneral.spComando.Parameters.Add("pva_linea", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value =CODGRP_MAT+CODSUB_MAT;
                MDOgeneral.spComando.Parameters["pva_linea"].Direction = ParameterDirection.Input;
                //Output
                MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;

                MDOgeneral.spComando.ExecuteNonQuery();
                return MDOgeneral.spComando.Parameters["pva_errusu"].Value.ToString();
            } 
            catch(Exception ex){ throw(ex); }        
        }

        public Boolean exiMov()
        {
            try
            {
                //Existencia materia primas
                string query = "select count(*) from MAEMTP where EXIMTP_MTP>0 and CODMAT_MTP='" + CODMAT_MAT + "'";
                if (!mod.consulta(query).Rows[0][0].ToString().Equals("0"))
                {
                    return true;
                }

                //Tiene Pedidos
                query = "select count(*) from DETPPRP where CODMAT_DPP='" + CODMAT_MAT + "'";
                if (!mod.consulta(query).Rows[0][0].ToString().Equals("0"))
                {
                    return true;
                }

                return false;
            }
            catch (Exception ex) { throw (ex); }
        }

        public DataTable repMat = new DataTable();
        public DataTable repGru = new DataTable();
        public void RepEntrSal(DateTime desde,DateTime hasta, string codprv,string codgru,string codsub,string codmat, string strColorInicial, string strColorFinal) 
        {
            try 
            {
                MDOgeneral.spComando.CommandText = "SP_INFENTSALP";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();
               
                //Input

                MDOgeneral.spComando.Parameters.Add("pdt_desde", Oracle.DataAccess.Client.OracleDbType.Date, ParameterDirection.Input).Value =desde;
                MDOgeneral.spComando.Parameters["pdt_desde"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pdt_hasta", Oracle.DataAccess.Client.OracleDbType.Date, ParameterDirection.Input).Value =hasta;
                MDOgeneral.spComando.Parameters["pdt_hasta"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_prvini", Oracle.DataAccess.Client.OracleDbType.Varchar2,2000).Value = String.IsNullOrEmpty(codprv)?"  ":codprv;
                MDOgeneral.spComando.Parameters["pva_prvini"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_prvfin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(codprv) ? "ZZ" : codprv;
                MDOgeneral.spComando.Parameters["pva_prvfin"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_grpini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(codgru) ? "  " : codgru;
                MDOgeneral.spComando.Parameters["pva_grpini"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_grpfin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(codgru) ? "ZZ" : codgru;
                MDOgeneral.spComando.Parameters["pva_grpfin"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_subini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(codsub) ? "  " : codsub;
                MDOgeneral.spComando.Parameters["pva_subini"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_subfin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(codsub) ? "ZZ" : codsub;
                MDOgeneral.spComando.Parameters["pva_subfin"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_matini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(codmat) ? "      " : codmat;
                MDOgeneral.spComando.Parameters["pva_matini"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_matfin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(codmat) ? "ZZZZZZ" : codmat;
                MDOgeneral.spComando.Parameters["pva_matfin"].Direction = ParameterDirection.Input;
                
                //FJRF - Nuevos paramatros incluidos por Mauricio
                MDOgeneral.spComando.Parameters.Add("pva_colini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(strColorInicial) ? "    " : strColorInicial;//FJRF - Se valida que si viene nulo se envien 4 espacios en blanco
                MDOgeneral.spComando.Parameters["pva_colini"].Direction = ParameterDirection.Input;
                //FJRF - Nuevos paramatros incluidos por Mauricio
                MDOgeneral.spComando.Parameters.Add("pva_colfin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = String.IsNullOrEmpty(strColorFinal) ? "ZZZZ" : strColorFinal;//FJRF - Se valida que si viene nulo se envien 4 Z en blanco
                MDOgeneral.spComando.Parameters["pva_colfin"].Direction = ParameterDirection.Input;

                //Output
                MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                
                MDOgeneral.spComando.ExecuteNonQuery();

                string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();

                repMat = mod.consulta("select * from tmpentsal1 order by NOMMAT_TMP, NOMCOL_TMP, UNICOM_TMP");
                repGru = mod.consulta("select * from tmpentsal2 order by NOMGRP_TMP, NOMSUB_TMP, NOMUNI_TMP, TIPMOV_TMP");
            }
            catch (Exception ex) { throw ex; }
        }



        public DataTable repDetMat = new DataTable();
        public void RepDetEntrSal(DateTime desde, DateTime hasta, string codmat, string codcol, string tipo)
        {
            try
            {
                MDOgeneral.spComando.CommandText = "SP_DETENTSAL";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();
                //Input
                MDOgeneral.spComando.Parameters.Add("pdt_desde", Oracle.DataAccess.Client.OracleDbType.Date, ParameterDirection.Input).Value = desde;
                MDOgeneral.spComando.Parameters["pdt_desde"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pdt_hasta", Oracle.DataAccess.Client.OracleDbType.Date, ParameterDirection.Input).Value = hasta;
                MDOgeneral.spComando.Parameters["pdt_hasta"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_codmat", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = codmat;
                MDOgeneral.spComando.Parameters["pva_codmat"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_codcol", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = codcol;
                MDOgeneral.spComando.Parameters["pva_codcol"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_tipo", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value =tipo;
                MDOgeneral.spComando.Parameters["pva_tipo"].Direction = ParameterDirection.Input;
                //Output
                MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.ExecuteNonQuery();
                string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();
                repDetMat = mod.consulta("select * from tmpdetentsal");
            }
            catch (Exception ex) { throw ex; }
        }
        
        public DataTable repGenMat = new DataTable();
        public void RepGenMat(string ano,string mes, string grupos, string mostrarceros)
        {
            try
            {
                MDOgeneral.spComando.CommandText = "SP_INFGENMAT";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();
                //Input
                MDOgeneral.spComando.Parameters.Add("pnu_ano", Oracle.DataAccess.Client.OracleDbType.Int32, 40).Value = ano;
                MDOgeneral.spComando.Parameters["pnu_ano"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pnu_mes", Oracle.DataAccess.Client.OracleDbType.Int32, 40).Value = mes;
                MDOgeneral.spComando.Parameters["pnu_mes"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_grupos", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = grupos;
                MDOgeneral.spComando.Parameters["pva_grupos"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_cero", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Value = mostrarceros;
                MDOgeneral.spComando.Parameters["pva_cero"].Direction = ParameterDirection.Input;
                //Output
                MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.ExecuteNonQuery();
                string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();
                repGenMat = mod.consulta("select * from tmpinfgenmat");
            }
            catch (Exception ex) { throw ex; }
        }
                
        #endregion
    }
}
